package cn.net.comsys.vpn.web.util;
/**
 * @author JiF
 * @version 创建时间：2011-4-21 下午02:41:21
 * E-Mail:liaojifeng@163.com
 * 类说明：
 *
 *
 *
 */
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.naming.InitialContext;

import javax.naming.Context;
import javax.sql.DataSource;

//import org.apache.commons.dbcp.BasicDataSource;

/** */
/** 从tomcat中获取连接
 * @author jifeng
 * 
 */
public class DataBase {

	Context ctx = null;
	private Connection con = null;
	private DataSource ds = null;
	private Statement st = null;
	private ResultSet rs = null;
	//static int cishu=0;
	

	/**
	 * 从连接池获得数据源
	 */

	public DataBase() {
		try {
			
//			ctx = new InitialContext();
//			this.ds = (DataSource) ctx.lookup("java:comp/env/jdbc/oracle");
//			this.con = this.ds.getConnection();
			
			
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); 
			String url="jdbc:oracle:thin:@127.0.0.1:1521:UCIT"; //orcl为数据库的SID 
			String user="ucitdba"; 
			String password="111111"; 
			this.con= DriverManager.getConnection(url,user,password); 
			
		} catch (Exception e) { 
			e.printStackTrace();
			System.out.println("连接数据库有误！");
		}

	}
	
	public void executeCall(String callsql){
		//"{ call gx.siyu_jiangshui_procedures() }"
		try{
			CallableStatement proc = con.prepareCall(callsql);
			proc.execute();
		} catch (SQLException ex2) {
			ex2.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	
	
	
	
	public static void main(String[] args) {
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
			String url = "jdbc:oracle:thin:@220.167.220.15:1521:sjyrdb"; // orcl为数据库的SID
			String user = "gx";
			String password = "gx";
			conn = DriverManager.getConnection(url, user, password);
			CallableStatement proc = conn.prepareCall("{ call gx.siyu_jiangshui_h_qy() }");
			proc.execute();
		} catch (SQLException ex2) {
			ex2.printStackTrace();
		} catch (Exception ex2) {
			ex2.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					if (stmt != null) {
						stmt.close();
					}
					if (conn != null) {
						conn.close();
					}
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	
	
	
	
	

	/**
	 * 从逻辑BEAN获取SQL,返回ReultSet
	 * 
	 * @param sql
	 *            sql语句
	 * @return 结果集
	 * @throws SQLException
	 */
	public ResultSet getStatement(String sql) throws SQLException {
		st = con.createStatement(); 
		//System.out.println("连接成功");
		rs = st.executeQuery(sql);
		
		//System.out.println("执行sql成功");
		//System.out.println(rs);
		return rs;
	}
	/**
	 * 将ResultSet结构转换成list里面装Hashmap
	 * @param sql  sql语句
	 * @return 结果集
	 * @throws SQLException
	 */
	public List getselectRSmap(String sql) throws SQLException {
		Map rsTree;
		List rsall = new ArrayList();
		try {
			st = con.createStatement();
			// System.out.println("连接成功");
			rs = st.executeQuery(sql);

			ResultSetMetaData rsmd = rs.getMetaData();// 取数据库的列名 我觉得名比1，2，3..更好用
			int numberOfColumns = rsmd.getColumnCount();
			while (rs.next()) {
				rsTree = new HashMap(numberOfColumns);// 注意要new
				for (int r = 1; r < numberOfColumns + 1; r++) {
					rsTree.put(rsmd.getColumnName(r), rs.getObject(r));
				}
				rsall.add(rsTree);
			}
		} finally {
			closeconn(rs);
			closeconn(st);
			closeconn(con);
		}

		return rsall;
	}
	/**
	 * 将ResultSet结构转换成list里面装String[]
	 * @param sql	sql语句
	 * @return	结果集
	 * @throws SQLException
	 */
	public List getselectRSString(String sql) throws SQLException {

		List rsall = new ArrayList();
		try {
			st = con.createStatement();
			// System.out.println("连接成功");
			rs = st.executeQuery(sql);

			ResultSetMetaData rsmd = rs.getMetaData();// 取数据库的列名 我觉得名比1，2，3..更好用
			int numberOfColumns = rsmd.getColumnCount();
			String[] str;
			while (rs.next()) {
				str = new String[numberOfColumns];
				for (int r = 1; r < numberOfColumns + 1; r++) {
					// rsTree.put(rsmd.getColumnName(r), rs.getObject(r));
					// str[r-1]=rs.getString(r);
					if (rs.getString(r) != null)
						str[r - 1] = rs.getString(r);
					else
						str[r - 1] = "";

				}
				rsall.add(str);
			}
		} finally {
			closeconn(rs);
			closeconn(st);
			closeconn(con);
		}
		return rsall;
	}
	
	
	// 实现对表的增，删，改
	public int upTable(String sql) throws SQLException {
		st = con.createStatement();
		int i = st.executeUpdate(sql);
		return i;
	}

	// 创建事务和还原事务
	public void createCommit(boolean autoCommit) throws SQLException {
		con.setAutoCommit(autoCommit);
	}

	// 提交事务
	public void toCommit() throws SQLException {
		con.commit();
	}

	// 回滚事物
	public void backCommit() throws SQLException {
		con.rollback();
	}

	// 关闭Connection
	public void closeConnection() throws SQLException {
		if (con != null) {
			con.close();
		}
	}

	// 关闭Statement
	public void closeStatement() throws SQLException {
		if (st != null) {
			st.close();
		}
	}

	// 关闭ResultSet
	public void closeResultSet() throws SQLException {
		if (rs != null) {
			rs.close();
		}
	}

	public Connection getCon() {
	    return con;
	}
	
	
	
	private void closeconn(ResultSet rs){
		try {
				if (rs!=null)rs.close();
			} catch (SQLException e) {}
	}
	private void closeconn(Statement st){
		try {
				if (st!=null)st.close();
			} catch (SQLException e) {}
	}
	private void closeconn(Connection conn){
		try {
				if (conn!=null)conn.close();
			} catch (SQLException e) {}
	}

}
